#include <sqlite3.h>
#include <stdio.h>
#include <string.h>

#include "commonality.h"
#include "sqlite.h"
#include "server.h"

// 打开数据库
int sqlite_open(const char *pathdir, sqlite3 **ppDb)
{
	if (sqlite3_open(pathdir, ppDb) == SQLITE_OK) {
		printf("open is succeed.\n");
	} else {
		printf("%d: %s", sqlite3_errcode(*ppDb), sqlite3_errmsg(*ppDb));
		return sqlite3_errcode(*ppDb);
	}

	return 0;
}

/*
 * 'log_in'(name char primary key, password char, privilege int);
 * 'user'(name char primary key, no int, addr char, age int, level int, phone char, salary double);
 */

// 创建数据表
int sqlite_create(sqlite3 *ppDb, const char *name)
{
	char *errmsg;
	char buf[128];
	
	if (strcmp(name, ENTRY_TABLE) == 0) {	// 创建用户密码表
		sprintf(buf, "create table '%s'(name char primary key, password char, privilege int)", name);
	} else if (strcmp(name, INFO_TABLE) == 0) {	// 创建信息表
		sprintf(buf, "create table '%s'(name char primary key, no int, addr char, age int, level int, phone char, salary double)", name);
	} else {
		printf("Cannot create %s table.\n", name);
		return -1;
	}

	// 创建
	if(sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg) != SQLITE_OK) {
		printf("%s.\n", errmsg);
		return -1;
	} else {
		printf("the table is created succeed\n");
	}
	
	return 0;
}

// 向数据库中插入一条用户数据
int do_insert_user(sqlite3 *ppDb, const char *name, const char *password, int privilege)
{
	char buf[128];
	int val = 0;
	int score = 0;
	char *errmsg;
	
	sprintf(buf, "insert into log_in values ('%s', '%s', '%d')", name, password, privilege);
	
	if(sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg) != SQLITE_OK) {
		printf("%s.\n", errmsg);
		return -1;
	} else {
		printf("the datas is inserted successfully\n");
	}
	
	return 0;
}


//根据用户名、密码、和用户类型查询用户信息表
int do_query_user(sqlite3 *ppDb, const char *name, const char *password, int privilege)
{
	char buf[128];
	int nrow, ncolumn;
	char *errmsg, **result;
	
	sprintf(buf, "select * from log_in where name='%s'and password='%s' and privilege='%d'", name, password, privilege);
	
	if(sqlite3_get_table(ppDb, buf, &result, &nrow, &ncolumn, &errmsg) != SQLITE_OK) {
		printf("%s.\n", errmsg);
		return -1;
	}

	if(nrow == 0) {
		printf("name or password is wrong\n");
		return -1;
	}

	return 0;
}

//根据用户名查询用户 privilege
int do_match_priv(sqlite3 *ppDb, const char *name)
{
	char buf[128];
	int ncow, ncolumn;
	char *errmsg, **resultp; 
	int i;
	
	sprintf(buf, "select * from log_in where name='%s'", name);
	
	if(sqlite3_get_table(ppDb, buf, &resultp, &ncow, &ncolumn, &errmsg) != SQLITE_OK) {
		printf("%s.\n", errmsg);
		return -1;
	}
	for(i = ncolumn;i < ncow *( ncolumn + 1);i = i + ncolumn)
	{
		if(strcmp(resultp[i],name) == 0)
		{
			printf("the privilege : %s \n",resultp[i+2]);

			if(strcmp(resultp[i+2],"0") == 0)
			{
				return 0;
			}
			if(strcmp(resultp[i+2],"1") == 0)
			{
				return 1;
			}
			if(strcmp(resultp[i+2],"2") == 0)
			{
				return 2;
			}

		}
	}	
	return 10;
}


query_desc_t do_sqlite_query(sqlite3* ppDb,char *name,int level)
{
/*name  no  addr  age  level  phone  salary*/
	INFO info;
	query_desc_t query_info;

    char sql[N] = {};
	char *errmsg;
	char **resultp;
	int ncow;
	int ncolumn;
	int i = 0;
	int j = 0;

	memset(&query_info, 0, sizeof(query_info));
	sprintf(sql, "select * from user");

	if(sqlite3_get_table(ppDb, sql, &resultp, &ncow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		printf("%s\n", errmsg);
	}

	if(level == USER_LEVEL ||level == ADMIN_LEVEL ||level == SUPERUSER_LEVEL||level == USER_QUERY_OTHER) //用户查询自己的信息
	{

		for(i = 0;i < ncolumn;i++)
		{
			printf("%s\t",resultp[i]);

		}
		puts("");

		for(i = ncolumn;i < ncow *( ncolumn + 1);i = i + ncolumn)
		{
			if(strcmp(resultp[i],name) == 0)
			{
				for(j = 0;j < 7 ;j++)
				{
					printf("%s\t",resultp[i+j]);
					if(j == 0)
					{
					 strcpy(query_info.name,resultp[i+j]);
					}
					if(j ==1)
					{						
					 strcpy(query_info.no,resultp[i+j]);
 					}
					if(j ==2)
					{						
					 strcpy(query_info.addr,resultp[i+j]);
					}
					if(j ==3)
					{						
					 strcpy(query_info.age,resultp[i+j]);
					}
					if(j ==4)
					{						
					 strcpy(query_info.level,resultp[i+j]);
					}
					if(j ==5)
					{						
					 strcpy(query_info.phone,resultp[i+j]);
					}
					if(j ==6)
					{						
					 strcpy(query_info.salary,resultp[i+j]);
					}
				}

			}
		}
		puts("");

	} 
	return query_info;
}

int do_sqlite_alter(sqlite3* ppDb, USER* user, INFO* info, int flag)
{
	char buf[512];
	int val = 0;
	int score = 0;
	char *errmsg;
	
	sprintf(buf, "update log_in set name='%s' where name='%s'",info->name,user->name);
	sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg);
	sprintf(buf, "update user set name='%s' where name='%s'",info->name, user->name);
	sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg);
	if (user->password[0]) {
		sprintf(buf, "update log_in set password='%s' where name='%s'",user->password, user->name);
		sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg);
	}
	sprintf(buf, "update user set addr='%s' where name='%s'",info->addr, info->name);
	sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg);
	sprintf(buf, "update user set age='%d' where name='%s'",info->age, info->name);
	sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg);
	sprintf(buf, "update user set phone='%s' where name='%s'",info->phone, info->name);
	sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg);
	
	switch (flag) {
		case 1:	// 管理员
			
			sprintf(buf, "update user set level='%d' where name='%s'",info->level, info->name);
			sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg);
			sprintf(buf, "update user set no='%d' where name='%s'",info->no, info->name);
			sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg);
			sprintf(buf, "update user set salary='%lf' where name='%s'",info->salary, info->name);
			sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg);
		break;
		
		case 2:	// 超级用户
			
			sprintf(buf, "update user set level='%d' where name='%s'",info->level, info->name);
			sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg);
			sprintf(buf, "update user set no='%d' where name='%s'",info->no, info->name);
			sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg);
			sprintf(buf, "update user set salary='%lf' where name='%s'",info->salary, info->name);
			sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg);
			
		break;
	}
	
	return 0;
}

//向数据库中插入一条用户数据信息
int do_insert_user_info(sqlite3 *ppDb,const char *name,int no, char *addr,int age,int level,char *phone,double salary)
{
	char buf[512];
//	int val = 0;
//	int score = 0;
	char *errmsg;
	
	sprintf(buf, "insert into user values ('%s','%d','%s','%d','%d','%s','%lf')",name,no,addr,age,level,phone,salary);
	
	if(sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg) != SQLITE_OK) {
		printf("%s.\n", errmsg);
		return -1;
	} else {
		printf("the datas is inserted successfully\n");
	}
	
	return 0;
}

// 向数据库中插入一条用户数据
int do_sqlite_del(sqlite3 *ppDb, const char *name)
{
	char buf[128];
	int val = 0;
	int score = 0;
	char *errmsg;
	
	sprintf(buf, "delete from user where name='%s'", name);
	
	if(sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg) != SQLITE_OK) {
		printf("%s.\n", errmsg);
		return -1;
	} else {
		printf("the datas is delete successfully\n");
	}
	
	sprintf(buf, "delete from log_in where name='%s'", name);
	
	if(sqlite3_exec(ppDb, buf, NULL, NULL, &errmsg) != SQLITE_OK) {
		printf("%s.\n", errmsg);
		return -1;
	} else {
		printf("the datas is delete successfully\n");
	}
	
	return 0;
}


